﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using bdnt_pets_system_Entity;
using System.Data;

namespace bdnt_pets_system_Bo.DataManager
{
    public class ShopManger
    {
        //AccessHelperSQL accessHelper = AccessHelperSQL.GetInstance();
        AccessHelper accessHelper = AccessHelper.GetInstance();
        List<Tbl_Shop> ConvertTableToObjs(DataTable dt)
        {
            List<Tbl_Shop> returnlist = new List<Tbl_Shop>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_Shop
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    ShopNo = dr["ShopNo"] == DBNull.Value ? "" : dr["ShopNo"].ToString(),
                    ShopName = dr["ShopName"] == DBNull.Value ? "" : dr["ShopName"].ToString(),
                    en_ShopName = dr["en_ShopName"] == DBNull.Value ? "" : dr["en_ShopName"].ToString(),
                    Remark = dr["Remark"] == DBNull.Value ? "" : dr["Remark"].ToString()
                });
            }
            return returnlist;
        }

        public List<Tbl_Shop> GetObjListByCondition(Tbl_Shop obj, int pageSize, int currentPage)
        {
            StringBuilder sql = new StringBuilder("select row_number() over(order by Id desc) recordId,s.* from Tbl_Shop s where 1=1");

            if (obj != null)
            {
                if (!string.IsNullOrEmpty(obj.ShopNo))
                    sql = sql.AppendFormat(@" and ShopNo like '%{0}%'", obj.ShopNo);
                if (!string.IsNullOrEmpty(obj.ShopName))
                    sql = sql.AppendFormat(@" and ShopName like '%{0}%'", obj.ShopName);
            }

            int startNum = pageSize * (currentPage > 0 ? currentPage - 1 : 0);
            int endNum = pageSize * currentPage;
            sql.Insert(0, "select re.* from (");
            sql.AppendFormat(@") re where re.recordId>{0} and re.recordId<={1}", startNum, endNum);
            var dt = accessHelper.ExcuteDataTable(sql.ToString());
            return ConvertTableToObjs(dt);
        }

        public int GetShopObjListByConditionCount(Tbl_Shop obj)
        {
            StringBuilder sql = new StringBuilder("select count(0) as allCount from Tbl_Shop where 1=1");
            if (obj != null)
            {
                if (!string.IsNullOrEmpty(obj.ShopNo))
                    sql = sql.AppendFormat(@" and ShopNo like '%{0}%'", obj.ShopNo);
                if (!string.IsNullOrEmpty(obj.ShopName))
                    sql = sql.AppendFormat(@" and ShopName like '%{0}%'", obj.ShopName);
            }
            var dt = accessHelper.ExecuteScalar(sql.ToString());
            if (dt != null)
                return Int32.Parse(dt.ToString());
            return 0;
        }

        public bool SaveObj(Tbl_Shop cuObj)
        {
            string sql = "";
            if (cuObj.Id == 0)
                sql = string.Format(@"INSERT INTO Tbl_Shop
           (ShopNo,ShopName,en_ShopName,Remark)
            VALUES('{0}','{1}','{2}','{3}')", cuObj.ShopNo, cuObj.ShopName
              , cuObj.en_ShopName, cuObj.Remark);

            else sql = string.Format(@"UPDATE Tbl_Shop
               SET ShopNo ='{0}'
                  ,ShopName ='{1}'
                 ,en_ShopName = '{2}'
                 ,Remark = '{3}' 
             WHERE Id={4}", cuObj.ShopNo, cuObj.ShopName, cuObj.en_ShopName, cuObj.Remark, cuObj.Id);
            return accessHelper.ExcultData(sql);
        }

        public bool DeleteCuObj(Int32 objId)
        {
            string sql = string.Format(@"delete from Tbl_Shop where Id={0}", objId);
            return accessHelper.ExcultData(sql);
        }
    }
}
